#include "myserver.h"
#include <QApplication>


void insertDataToMySQL(QAxObject *sheet) {
    QSqlQuery query;
    query.prepare("INSERT INTO QuestionBank (ID, topic, opA, opB, opC, opD, rig,score) VALUES (?, ?, ?, ?, ?, ?, ?,?)");

    int row = 2; // 假设第一行是标题，从第二行开始读取数据
    while (true) {
        QAxObject *cell1 = sheet->querySubObject("Cells(int,int)", row, 1); // 读取第row行第1列
        QAxObject *cell2 = sheet->querySubObject("Cells(int,int)", row, 2); // 读取第row行第2列
        QAxObject *cell3 = sheet->querySubObject("Cells(int,int)", row, 3); // 读取第row行第3列 (opA)
        QAxObject *cell4 = sheet->querySubObject("Cells(int,int)", row, 4); // 读取第row行第4列 (opB)
        QAxObject *cell5 = sheet->querySubObject("Cells(int,int)", row, 5); // 读取第row行第5列 (opC)
        QAxObject *cell6 = sheet->querySubObject("Cells(int,int)", row, 6); // 读取第row行第6列 (opD)
        QAxObject *cell7 = sheet->querySubObject("Cells(int,int)", row, 7); // 读取第row行第7列 (rig)
        QAxObject *cell8 = sheet->querySubObject("Cells(int,int)", row, 8); // 读取第row行第8列 (score)

        QVariant value1 = cell1->property("Value");
        QVariant value2 = cell2->property("Value");
        QVariant value3 = cell3->property("Value");
        QVariant value4 = cell4->property("Value");
        QVariant value5 = cell5->property("Value");
        QVariant value6 = cell6->property("Value");
        QVariant value7 = cell7->property("Value");
        QVariant value8 = cell8->property("Value");

        if (value1.isNull() || value2.isNull() || value3.isNull() || value4.isNull() || value5.isNull() || value6.isNull() || value7.isNull()|| value8.isNull()) {
            break; // 如果任何一个单元格为空，退出循环
        }

        query.addBindValue(value1);
        query.addBindValue(value2);
        query.addBindValue(value3);
        query.addBindValue(value4);
        query.addBindValue(value5);
        query.addBindValue(value6);
        query.addBindValue(value7);
        query.addBindValue(value8);

        if (!query.exec()) {
            qDebug() << "Failed to insert data at row" << row << ":" << query.lastError().text();
            break; // 可选：在出错时停止插入
        }

        row++;
    }

    qDebug() << "Data insertion completed!";
}

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MyServer w;
    w.StartServer(8080);

//    return a.exec();


        QString filePath = "F:\\qt----text\\ExamMonitoringSystem\\考试监控系统题库.xlsx";

        QAxObject excel("Excel.Application"); // 创建Excel应用程序对象
        excel.setProperty("Visible", false);  // 设置Excel不可见

        QAxObject *workbooks = excel.querySubObject("Workbooks"); // 获取工作簿集合
        QAxObject *workbook = workbooks->querySubObject("Open(const QString&)", filePath); // 打开Excel文件
        QAxObject *sheets = workbook->querySubObject("Worksheets"); // 获取工作表集合

        // 获取第一个工作表
        QAxObject *sheet = sheets->querySubObject("Item(int)", 1);

//        if (!connectToMySQL()) {
//            return -1;
//        }

        insertDataToMySQL(sheet);

        // 关闭工作簿并退出Excel
        workbook->dynamicCall("Close()");
        excel.dynamicCall("Quit()");

        // 释放内存
        delete sheet;
        delete sheets;
        delete workbook;
        delete workbooks;

        return a.exec();
}
